﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Statistics.BLL;
using System.Data;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Globalization;

namespace Statistics
{
    public class StatisticsController
    {
        public StatisticsController()
        {

        }

        public List<UsersStats> GetUsersSubscriptionCount()
        {
            List<UsersStats> retValue = new List<UsersStats>();

            Model.StatisticsEntities ctx = new Model.StatisticsEntities();
            System.Data.Objects.ObjectResult<Statistics.Model.GetUsersCount_Result> obj = ctx.GetUsersCount(null, null, null);

            foreach (Statistics.Model.GetUsersCount_Result o in obj)
            {
                retValue.Add(new UsersStats() { SiteName = o.SiteName, UserCounts = o.userCount.GetValueOrDefault(0) });
            }

            return retValue;
        }

        public List<UsersStats> GetUsersSubscriptionCount__()
        {
            List<UsersStats> retValue = new List<UsersStats>();

            retValue.Add(new UsersStats() { SiteName = "BPP", UserCounts = 300 });
            retValue.Add(new UsersStats() { SiteName = "EDG", UserCounts = 200 });
            retValue.Add(new UsersStats() { SiteName = "FDG", UserCounts = 150 });
            retValue.Add(new UsersStats() { SiteName = "GEL", UserCounts = 100 });
            retValue.Add(new UsersStats() { SiteName = "JOY", UserCounts = 135 });
            retValue.Add(new UsersStats() { SiteName = "XXX", UserCounts = 100 });
            retValue.Add(new UsersStats() { SiteName = "XXX1", UserCounts = 100 });
            retValue.Add(new UsersStats() { SiteName = "XXX2", UserCounts = 100 });

            return retValue;
        }

        public List<UsersStats<DateTime, UsersStats>> GetUsersInPeriod(DateTime? from, DateTime? to, string siteCode)
        {
            List<UsersStats<DateTime, UsersStats>> retValue = null;

            DataTable dt = CreateDatatable();

            retValue = new List<UsersStats<DateTime, UsersStats>>();
            for(int j=1; j<dt.Columns.Count; j++)
            {
                UsersStats<DateTime, UsersStats> us = new UsersStats<DateTime, UsersStats>();
                us.Values = new UsersStats[dt.Rows.Count];

                string m_k = dt.Columns[j].Caption.ToString();
                us.Key = DateTime.Parse(m_k);

                for (int i=0; i<dt.Rows.Count; i++)
                {
                    us.Values[i] = new UsersStats() { SiteName = dt.Rows[i][0].ToString(), UserCounts = int.Parse(dt.Rows[i][j].ToString().ToString()) };
                }

                retValue.Add(us);
            }

            return retValue;
        }

        public DataTable GetUsersInPeriod2(DateTime? from, DateTime? to, string siteCode)
        {
            CultureInfo currentCulture = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Model.StatisticsEntities ctx = new Model.StatisticsEntities();
            var obj = ctx.GetUsersInPeriod2(from, to, siteCode);
            List<Model.GetUsersInPeriod2_Result> theList = obj.ToList();

            System.Threading.Thread.CurrentThread.CurrentCulture = currentCulture;

            DataTable dt = theList.ListToDataTable();

            DataTable dt1 = dt.Pivot(dt.Columns["SiteName"], dt.Columns["Presenze"]);

            return dt1;
        }

        public DataTable GetUsersInPeriodWithDatable(DateTime? from, DateTime? to, string siteCode)
        {
            DataTable retValue = null; // CreateDatatable2();

            //var dynamicTable = retValue.AsDynamicEnumerable();
            //string key = dynamicTable.First().Key;
            //int value = dynamicTable.First().data1;

            try
            {
                retValue = new DataTable("Dati");

                Model.StatisticsEntities ctx = new Model.StatisticsEntities();
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(((EntityConnection)ctx.Connection).StoreConnection.ConnectionString))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        using (SqlCommand cmd = new SqlCommand("dbo.Provadinamica", con))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;

                            //cmd.Parameters.AddWithValue("@DataFrom", from);
                            //cmd.Parameters.AddWithValue("@DataTo", to);
                            //cmd.Parameters.AddWithValue("@SiteCode", siteCode);

                            con.Open();
                            sda.SelectCommand = cmd;
                            sda.Fill(retValue);
                        }
                    }
                }

                int ret = dt.Rows.Count;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return retValue;
        }

        public SiteInfo[] GetSiteNames()
        {
            SiteInfo[] retValue = null;

            Model.StatisticsEntities ctx = new Model.StatisticsEntities();

            var objs = from x in ctx.aspnet_Applications select new SiteInfo { Id=x.ApplicationId, SiteName=x.ApplicationName };

            if (objs.Any())
            {
                retValue = objs.ToArray();
            }
            
            return retValue;
        }

        public string[] GetSiteNames__()
        {
            string[] retValue = new string[] { "BPP", "EDG", "FDG", "GEL", "JOY" };
            return retValue;
        }

        /// <summary>
        /// Create DataTable with data...
        /// </summary>
        /// <remarks>
        /// 
        ///    structure of data:
        /// 
        ///    Data  BPP  EDG  FDG  GEL  JOY
        ///    5     13   0    0    3    1 
        ///    6     17   0    1    2    0
        ///    7     9    0    1    1    0
        ///    8     12   2    0    1    0
        ///    9     9    0    1    1    0
        ///    10    14   0    0    4    1
        ///    11    11   0    0    1    0
        ///    12    18   0    1    4    0
        ///    13    12   0    0    1    0
        /// 
        /// </remarks>
        private DataTable CreateDatatable2()
        {
            DataTable dt = new DataTable("DatiChart");
            dt.Columns.Add("Data", typeof(DateTime));
            dt.Columns.Add("BPP", typeof(int));
            dt.Columns.Add("EDG", typeof(int));
            dt.Columns.Add("FDG", typeof(int));
            dt.Columns.Add("GEL", typeof(int));
            dt.Columns.Add("JOY", typeof(int));

            CreateDataRow2(dt, new DateTime(2013, 6, 5), 13, 0, 0, 3, 1);
            CreateDataRow2(dt, new DateTime(2013, 6, 6), 17, 0, 1, 2, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 7), 9, 0, 1, 1, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 8), 12, 2, 0, 1, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 9), 9, 0, 1, 1, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 10), 14, 0, 0, 4, 1);
            CreateDataRow2(dt, new DateTime(2013, 6, 11), 11, 0, 1, 1, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 12), 18, 0, 1, 4, 0);
            CreateDataRow2(dt, new DateTime(2013, 6, 13), 12, 0, 0, 1, 0);

            return dt;
        }

        private void CreateDataRow2(DataTable table, DateTime data, int n1, int n2, int n3, int n4, int n5)
        {
            DataRow r1 = table.NewRow();

            r1["Data"] = data;
            r1["BPP"] = n1;
            r1["EDG"] = n2;
            r1["FDG"] = n3;
            r1["GEL"] = n4;
            r1["JOY"] = n5;

            table.Rows.Add(r1);
        }

        /// <summary>
        /// Create DataTable with data...
        /// </summary>
        /// <remarks>
        /// 
        ///    structure of data:
        /// 
        ///    ApplicationName            5    6    7    8    9   10   11   12   13
        ///    ---------------------------------------------------------------------
        ///    BPP                       13   17    9   12    9   14   11   18   12
        ///    EDG                        0    0    0    2    0    0    0    0    0
        ///    FDG                        0    1    1    0    1    0    0    1    0
        ///    GEL                        3    2    1    1    1    4    1    4    1
        ///    JOY                        1    0    0    0    0    1    0    0    0
        ///    
        /// 
        /// 
        ///    Data  BPP  EDG  FDG  GEL  JOY
        ///    5     13   0    0    3    1 
        ///    6     17   0    1    2    0
        ///    7     9    0    1    1    0
        ///    8     12   2    0    1    0
        ///    9     9    0    1    1    0
        ///    10    14   0    0    4    1
        ///    11    11   0    0    1    0
        ///    12    18   0    1    4    0
        ///    13    12   0    0    1    0
        /// 
        /// </remarks>
        private DataTable CreateDatatable()
        {
            DataTable dt = new DataTable("DatiChart");
            dt.Columns.Add("Key", typeof(string));
            dt.Columns.Add("Data1", typeof(int));
            dt.Columns.Add("Data2", typeof(int));
            dt.Columns.Add("Data3", typeof(int));
            dt.Columns.Add("Data4", typeof(int));
            dt.Columns.Add("Data5", typeof(int));
            dt.Columns.Add("Data6", typeof(int));
            dt.Columns.Add("Data7", typeof(int));
            dt.Columns.Add("Data8", typeof(int));
            dt.Columns.Add("Data9", typeof(int));

            CreateDataRow(dt, "BPP", 13, 17, 9, 12, 9, 14, 11, 18, 12);
            CreateDataRow(dt, "EDG", 0, 0, 0, 2, 0, 0, 0, 0, 0);
            CreateDataRow(dt, "FDG", 0, 1, 1, 0, 1, 0, 0, 1, 0);
            CreateDataRow(dt, "GEL", 3, 2, 1, 1, 1, 4, 1, 4, 1);
            CreateDataRow(dt, "JOY", 1, 0, 0, 0, 0, 1, 0, 0, 0);

            return dt;
        }

        private void CreateDataRow(DataTable table, string key, int n1, int n2, int n3, int n4, int n5, int n6, int n7, int n8, int n9)
        {
            DataRow r1 = table.NewRow();

            r1["Key"] = key;
            r1["Data1"] = n1;
            r1["Data2"] = n2;
            r1["Data3"] = n3;
            r1["Data4"] = n4;
            r1["Data5"] = n5;
            r1["Data6"] = n6;
            r1["Data7"] = n7;
            r1["Data8"] = n8;
            r1["Data9"] = n9;
            
            table.Rows.Add(r1);
        }
    }
}
